package com.eascs.web.o2o.web.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSON;
import com.eascs.app.context.ConfigContext;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.dao.sql.ObjectSqlBuilder;
import com.eascs.common.util.StringUtil;
import com.eascs.report.common.entity.ProvinceTeamTotalForm;
import com.eascs.report.common.entity.ReportStatForm;
import com.eascs.web.o2o.web.dao.BidmDao;
import com.eascs.web.o2o.web.dao.BiodsDao;
import com.eascs.web.o2o.web.dao.WebAdminDao;
import com.eascs.web.o2o.web.dao.WebBaseDao;
import com.eascs.web.o2o.web.form.LoanForProvinceForm;
import com.eascs.web.o2o.web.service.ReportStatService;

@Service
public class ReportStatServiceImpl implements ReportStatService {
	@Autowired
	WebBaseDao webBaseDao;

	@Autowired
	WebAdminDao webAdminDao;

	
	@Autowired
	BidmDao bidmDao;
	
	@Autowired
	BiodsDao biodsDao;
	
	
	
	// 根据平台查询报表
	@Override
	public PageResult<Object[]> getReportStatByPlatformByPage(ReportStatForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		ObjectSqlBuilder objectSqlBuilder = ObjectSqlBuilder.create(entity, ConfigContext.getStringValue("sql.ReportStatServiceImpl.reportStatByPlatform"));
		sqlPageQuery.setQuerySql(objectSqlBuilder.getSql()+" GROUP BY ltdc ORDER BY loanamount DESC ");
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParams(objectSqlBuilder.getParams());
		sqlPageQuery.setSumColumnIndexs(new int[] { 2, 3, 4, 5, 6 });
		return biodsDao.findDataWithPage(sqlPageQuery);
	}
	// 根据经办人查询报表
	@Override
	public PageResult<Object[]> getReportStatByOperatorByPage(ReportStatForm entity){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		ObjectSqlBuilder objectSqlBuilder = ObjectSqlBuilder.create(entity,"");
		if(StringUtil.isEmpty(entity.getPlatform())){
			sqlPageQuery.setQuerySql( ConfigContext.getStringValue("sql.ReportStatServiceImpl.reportStatByOperator")+objectSqlBuilder.getSql()+" GROUP by operator_name ORDER BY loanamount DESC ");
		}else{
			sqlPageQuery.setQuerySql( ConfigContext.getStringValue("sql.ReportStatServiceImpl.reportStatByOperator")+" and ltdc = ? "+objectSqlBuilder.getSql()+" GROUP by operator_name ORDER BY loanamount DESC ");
		}
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		if(!StringUtil.isEmpty(entity.getPlatform())){
			sqlPageQuery.addParam(entity.getLtd());
		}
		sqlPageQuery.addParams(objectSqlBuilder.getParams());
		sqlPageQuery.setSumColumnIndexs(new int[] { 2, 3, 4, 5, 6 });
		return biodsDao.findDataWithPage(sqlPageQuery);
	}

	// 根据放款银行查询报表
	@Override
	public PageResult<Object[]> getReportStatByBankreFundByPage(ReportStatForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(),
				entity.getO2oIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append("sql.ReportStatServiceImpl.reportStatByBankreFund");

		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.setSumColumnIndexs(new int[] { 1, 2, 3, 4, 5 });

		sqlPageQuery.setQuerySql(querySql.toString());
		return biodsDao.findDataWithPage(sqlPageQuery);
	}

	// 根据省区查询报表
	@Override
	public PageResult<Object[]> getReportStatByProvinceByPage(ReportStatForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		ObjectSqlBuilder objectSqlBuilder = ObjectSqlBuilder.create(entity, ConfigContext.getStringValue("sql.ReportStatServiceImpl.reportStatByProvince"));
		sqlPageQuery.setQuerySql(objectSqlBuilder.getSql()+" GROUP BY regional_name ORDER BY loanamount DESC ");
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.setSumColumnIndexs(new int[] { 1, 2, 3, 4, 5 });
		sqlPageQuery.addParams(objectSqlBuilder.getParams());
		
		return biodsDao.findDataWithPage(sqlPageQuery);
	}


	//信贷经理放款清单(添加在职状态和入职时间)
	@Override
	public PageResult<Object[]> getReportStatByDimissionByPage(ReportStatForm entity){
		// String sql = getSql();
		StringBuffer  sql=new StringBuffer();
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		// ObjectSqlBuilder objectSqlBuilder = ObjectSqlBuilder.create(entity, ConfigContext.getStringValue("sql.ReportStatServiceImpl.reportStatByOperator"));
        sql.append(biodsDao.getSql("sql.ReportStatServiceImpl.reportStatByOperator"));
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());

		if(StringUtil.isNotBlank(entity.getIsDimission())){
			sql.append(" AND  is_dimission=? ");
			sqlPageQuery.addParam(entity.getIsDimission());
		}
		if(null != entity.getJoinDateStart()){
			sql.append(" AND  join_date>=? ");
			sqlPageQuery.addParam(entity.getJoinDateStart());
		}
		if(null != entity.getJoinDateEnd()){
			sql.append(" AND  join_date<=? ");
			sqlPageQuery.addParam(entity.getJoinDateEnd());
		}
		
      /*
		if(StringUtil.isEmpty(entity.getPlatform())){
			sqlPageQuery.setQuerySql( sql+objectSqlBuilder.getSql()+" GROUP by operator_name ORDER BY loanamount DESC ");
		}else{
			sqlPageQuery.setQuerySql( sql+" and ltdc = ? "+objectSqlBuilder.getSql()+" GROUP by operator_name ORDER BY loanamount DESC ");
		}
		if(!StringUtil.isEmpty(entity.getPlatform())){
			sqlPageQuery.addParam(entity.getLtd());
		}
		
		sqlPageQuery.addParams(objectSqlBuilder.getParams());
		*/
		
		if(!StringUtil.isEmpty(entity.getPlatform())){
			sql.append(" and ltdc = ? " );
			sqlPageQuery.addParam(entity.getLtd());
		}
		
		if(StringUtil.isNotBlank(entity.getProvince())){
			sql.append(" and province_name = ? ");
			sqlPageQuery.addParam(entity.getProvince());
		}
		
		if(StringUtil.isNotBlank(entity.getOperatorName())){ 
			sql.append(" and operator_name like ? ");
			sqlPageQuery.addLikeParam(entity.getOperatorName()); 
		}
		
		sql.append(" GROUP by operator_name ORDER BY loanamount DESC ");
		sqlPageQuery.setQuerySql(sql.toString());		
		sqlPageQuery.setSumColumnIndexs(new int[] { 4, 5, 6, 7, 8 });
		return biodsDao.findDataWithPage(sqlPageQuery);
	}




	private static  String getSql(){
		return  "SELECT \n" +
				"\t\t\t\toperator_name AS operator_name, \n" +
				"\t\t\t\tregional_name, \n" +
				"\t\t\t\t  join_date, case is_dimission when 0 then '在职' else '离职' end as is_dimission , \n" +
				"\t\t\t\tSUM(zxstartdate ) zxstartdate, \n" +
				"\t\t\t\tSUM(zxenddate) zxenddate, \n" +
				"\t\t\t\tSUM(nsstartdate) nsstartdate, \n" +
				"\t\t\t\tSUM(nsenddate) nsenddate, \n" +
				"\t\t\t\tSUM(brtenddate) brtenddate, \n" +
				"\t\t\t\tSUM(loandate) loandate, \n" +
				"\t\t\t\tSUM(loanamount) / 10000 loanamount \n" +
				"\t\t\t\tFROM \n" +
				"\t\t\t\t( \n" +
				"\t\t\t\tSELECT \n" +
				"\t\t\t\tt.ltd AS ltdc, \n" +
				"\t\t\t\tp.platform_name AS ltd, \n" +
				"\t\t\t\tt.ltd_code AS ltdcode, \n" +
				"\t\t\t\tt.operator_name, \n" +
				"\t\t\t\tp.province_name regional_name, \n" +
				"\t\t\t\tt.join_date, t.is_dimission, \n" +
				"\t\t\t\tCASE \n" +
				"\t\t\t\tWHEN t.wf_type != 'NS' \n" +
				"\t\t\t\tAND t.wf_type != 'BRT' \n" +
				"\t\t\t\tAND t.start_date >=? \n" +
				"\t\t\t\tAND t.start_date < date_add(?, INTERVAL 1 DAY) THEN \n" +
				"\t\t\t\t1 \n" +
				"\t\t\t\tELSE \n" +
				"\t\t\t\t0 \n" +
				"\t\t\t\tEND AS zxstartdate, \n" +
				"\t\t\t\tCASE \n" +
				"\t\t\t\tWHEN t.wf_type != 'NS' \n" +
				"\t\t\t\tAND t.wf_type != 'BRT' \n" +
				"\t\t\t\tAND t.end_date >=? \n" +
				"\t\t\t\tAND t.end_date < date_add(?, INTERVAL 1 DAY) THEN \n" +
				"\t\t\t\t1 \n" +
				"\t\t\t\tELSE \n" +
				"\t\t\t\t0 \n" +
				"\t\t\t\tEND AS zxenddate, \n" +
				"\t\t\t\tCASE \n" +
				"\t\t\t\tWHEN t.wf_type = 'NS' \n" +
				"\t\t\t\tAND t.start_date >=? \n" +
				"\t\t\t\tAND t.start_date < date_add(?, INTERVAL 1 DAY) THEN \n" +
				"\t\t\t\t1 \n" +
				"\t\t\t\tELSE \n" +
				"\t\t\t\t0 \n" +
				"\t\t\t\tEND AS nsstartdate, \n" +
				"\t\t\t\t CASE \n" +
				"\t\t\t\tWHEN t.wf_type = 'NS' \n" +
				"\t\t\t\tAND t.end_date >=? \n" +
				"\t\t\t\tAND t.end_date < date_add(?, INTERVAL 1 DAY) THEN \n" +
				"\t\t\t\t1 \n" +
				"\t\t\t\tELSE \n" +
				"\t\t\t\t0 \n" +
				"\t\t\t\tEND AS nsenddate, \n" +
				"\t\t\t\t CASE \n" +
				"\t\t\t\tWHEN t.wf_type = 'BRT' \n" +
				"\t\t\t\tAND t.end_date >=? \n" +
				"\t\t\t\tAND t.end_date < date_add(?, INTERVAL 1 DAY) THEN \n" +
				"\t\t\t\t1 \n" +
				"\t\t\t\tELSE \n" +
				"\t\t\t\t0 \n" +
				"\t\t\t\tEND AS brtenddate, \n" +
				"\t\t\t\t CASE \n" +
				"\t\t\t\tWHEN t.loan_date >=? \n" +
				"\t\t\t\tAND t.loan_date < date_add(?, INTERVAL 1 DAY) THEN \n" +
				"\t\t\t\t1 \n" +
				"\t\t\t\tELSE \n" +
				"\t\t\t\t0 \n" +
				"\t\t\t\tEND AS loandate, \n" +
				"\t\t\t\t CASE \n" +
				"\t\t\t\tWHEN t.loan_date >=? \n" +
				"\t\t\t\tAND t.loan_date < date_add(?, INTERVAL 1 DAY) THEN \n" +
				"\t\t\t\tt.loan_amount \n" +
				"\t\t\t\tELSE \n" +
				"\t\t\t\t0 \n" +
				"\t\t\t\tEND AS loanamount \n" +
				"\t\t\t\tFROM \n" +
				"\t\t\t\t( \n" +
				"\t\t\t\tSELECT (\n" +
				"\tSELECT cm.join_date from credit_manager cm where cm.user_no=wf.operator_no \n" +
				") as join_date,\n" +
				"\t\t\t(\n" +
				"\tSELECT cm.is_dimission from credit_manager cm where cm.user_no=wf.operator_no \n" +
				") as is_dimission, wf.* FROM ( \n" +
				"\t\t\t\tSELECT\n" +
				"\t\t\t\t\t\tw.*, z.ltd_code,\n" +
				"\t\t\t\t\t\tz.ltd,\n" +
				"\t\t\t\t\t\tz.operator_name,\n" +
				"\t\t\t\t\t\tz.operator_no,\n" +
				"\t\t\t\t\t\tNULL loan_date,\n" +
				"\t\t\t\t\t\tNULL loan_amount\n" +
				"\t\t\t\t\tFROM\n" +
				"\t\t\t\t\t\twf_type_status w\n" +
				"\t\t\t\t\tINNER JOIN o2o_loanapply z ON w.loanapply_no = z.loanapply_no\n" +
				"\t\t\t\t\tAND w.wf_type = 'ZX'\n" +
				"\t\t\t\t\tWHERE\n" +
				"\t\t\t\t\t\tz.islock = 'N'\n" +
				"\t\t\t\t\tAND z.loan_type IN (\n" +
				"\t\t\t\t\t\t'BK',\n" +
				"\t\t\t\t\t\t'BP',\n" +
				"\t\t\t\t\t\t'BP_JX',\n" +
				"\t\t\t\t\t\t'SG',\n" +
				"\t\t\t\t\t\t'YC',\n" +
				"\t\t\t\t\t\t'SC'\n" +
				"\t\t\t\t\t)\n" +
				"\t\t\t\tUNION \n" +
				"\t\t\t\tSELECT \n" +
				"\t\t\t\tw.*, z.ltd_code, \n" +
				"\t\t\t\tz.ltd, \n" +
				"\t\t\t\tz.operator_name, \n" +
				"\t\t\t\tz.operator_no, \n" +
				"\t\t\t\tNULL loan_date, \n" +
				"\t\t\t\tNULL loan_amount \n" +
				"\t\t\t\tFROM \n" +
				"\t\t\t\twf_type_status w \n" +
				"\t\t\t\tINNER JOIN o2o_loanapply z ON w.loanapply_no = z.loanapply_no \n" +
				"\t\t\t\tAND w.wf_type = 'NS' \n" +
				"\t\t\t\tWHERE \n" +
				"\t\t\t\tz.islock = 'N' \n" +
				"\t\t\t\tAND z.loan_type IN ( \n" +
				"\t\t\t\t'BK', \n" +
				"\t\t\t\t'BP', \n" +
				"\t\t\t\t'BP_JX', \n" +
				"\t\t\t\t'SG', \n" +
				"\t\t\t\t'YC', \n" +
				"\t\t\t\t'SC' \n" +
				"\t\t\t\t) \n" +
				"\t\t\t\tUNION \n" +
				"\t\t\t\tSELECT \n" +
				"\t\t\t\tw.*, z.ltd_code, \n" +
				"\t\t\t\tz.ltd, \n" +
				"\t\t\t\tz.operator_name, \n" +
				"\t\t\t\tz.operator_no, \n" +
				"\t\t\t\tb.loan_date, \n" +
				"\t\t\t\tb.loan_amount \n" +
				"\t\t\t\tFROM \n" +
				"\t\t\t\twf_type_status w \n" +
				"\t\t\t\tINNER JOIN o2o_loanapply z ON w.loanapply_no = z.loanapply_no \n" +
				"\t\t\t\tAND w.wf_type = 'BRT' \n" +
				"\t\t\t\tINNER JOIN o2o_bank_loan_data b ON z.bill_code = b.bill_code \n" +
				"\t\t\t\tWHERE \n" +
				"\t\t\t\tz.islock = 'N' \n" +
				"\t\t\t\tAND z.loan_type IN ( \n" +
				"\t\t\t\t'BK', \n" +
				"\t\t\t\t'BP', \n" +
				"\t\t\t\t'BP_JX', \n" +
				"\t\t\t\t'SG', \n" +
				"\t\t\t\t'YC', \n" +
				"\t\t\t\t'SC' \n" +
				"\t\t\t\t) \n" +
				"\t\t\t\t ) wf  \n" +
				"\t\t\t\t) t \n" +
				"\t\t\t\tLEFT JOIN o2o_platform_regional p ON t.ltd = p.platform_id \n" +
				"\t\t\t\t \n" +
				"\t\t\t\tUNION ALL \n" +
				"\t\t\t\tSELECT 0 AS ltdc, 0 AS ltd, 0 AS ltdcode,user_name as operator_name,  \n" +
				"\t\t\t\tprovince_name as regional_name, join_date, case is_dimission when 0 then '在职' else '离职' end as is_dimission, 0 as zxstartdate,  \n" +
				"\t\t\t\t0 as zxenddate, 0 as nsstartdate, 0 as nsenddate, 0 as brtenddate, 0 as loandate,  \n" +
				"\t\t\t\t 0 as loanamount FROM credit_manager cm WHERE cm.user_no  not in( \n" +
				"\t\t\t\t SELECT l.operator_no FROM o2o_loanapply l WHERE l.operator_no>0 GROUP BY l.operator_no \n" +
				"\t\t\t\t) \n" +
				"\t\t\t\t) a  WHERE /**DATA_PERMISSION(ltdc)**/ 1=1 "
				;
	}
	
	
	
	
	
	
	
	@Override
	public PageResult<Object[]> getTeamTotalByPageForExcle(ProvinceTeamTotalForm entity) {
		StringBuffer  sql=new StringBuffer();
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		sql.append(bidmDao.getSql("sql.ReportStatServiceImpl.getTeamTotalByPageForExcle"));
		sqlPageQuery.addParams(new  Object[]{entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate()  });
		if(StringUtil.isNotBlank(entity.getProvince())){
			sql.append(" and  s.province_name = ? ");
			sqlPageQuery.addParam(entity.getProvince());
		}
		if(StringUtil.isNotBlank(entity.getAuditorNameNo())){ 
			sql.append(" and s.regional_director_no = ? ");
			sqlPageQuery.addParam(entity.getAuditorNameNo());
		}
		if(StringUtil.isNotBlank(entity.getAuditorName())){ 
			sql.append(" and s.regional_director = ? ");
			sqlPageQuery.addParam(entity.getAuditorName());
		}
		if(StringUtil.isNotBlank(entity.getIsDimission())){
			sql.append(" AND  s.regional_director_join_status = ?  ");
			sqlPageQuery.addParam(Integer.valueOf(entity.getIsDimission()));
		}
		if(StringUtil.isNotBlank(entity.getJoinDateStart())){
			sql.append(" AND  s.regional_director_join_date>=?");
			sqlPageQuery.addParam(entity.getJoinDateStart());
		}
		if(StringUtil.isNotBlank(entity.getJoinDateEnd())){
			sql.append(" AND  s.regional_director_join_date<=?  ");
			sqlPageQuery.addParam(entity.getJoinDateEnd());
		}
		if(StringUtil.isNotBlank(entity.getPostName())){
			sql.append(" AND  s.regional_director_post_name =?  ");
			sqlPageQuery.addParam(entity.getPostName());
		}
		sql.append(" group by s.regional_director_no,s.province_name order by monthSum desc");
		sqlPageQuery.setQuerySql(sql.toString());
		sqlPageQuery.setSumColumnIndexs(new int[] {5, 6,7,8,9,10,11,12,13,14});
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	
	@Override
	public PageResult<Object[]> getProvinceTotalByPage(ProvinceTeamTotalForm entity) {
		StringBuffer  sql=new StringBuffer();
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		sql.append(bidmDao.getSql("sql.ReportStatServiceImpl.getProvinceTotalByPage"));
		sqlPageQuery.addParams(new  Object[]{entity.getStatisticDate(),entity.getStatisticDate(), entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(), entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate()});
		if(StringUtil.isNotBlank(entity.getProvince())){
			sql.append(" and  t.province_name = ? ");
			sqlPageQuery.addParam(entity.getProvince());
		}
		sql.append(" group by t.province_name order by monthSum desc");
		sqlPageQuery.setQuerySql(sql.toString());
		sqlPageQuery.setSumColumnIndexs(new int[] { 1,2,3,4,5,6,7,8,9,10 });
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	
	
	
	@Override
	public PageResult<Object[]> getOperatorTotalByPageForExcle(ProvinceTeamTotalForm entity) {
		StringBuffer  sql=new StringBuffer();
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		sql.append(bidmDao.getSql("sql.ReportStatServiceImpl.getOperatorTotalByPageForExcle"));
		sqlPageQuery.addParams(new  Object[]{entity.getStatisticDate(),entity.getStatisticDate(),  entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),  entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate(),entity.getStatisticDate()});
		
		if(StringUtil.isNotBlank(entity.getProvince())){
			sql.append(" and  tt1.province_name = ? ");
			sqlPageQuery.addParam(entity.getProvince());
		}
		if(StringUtil.isNotBlank(entity.getAuditorNameNo())){
			sql.append(" and  tt1.regional_director_no = ?  ");
			sqlPageQuery.addParam(entity.getAuditorNameNo());
		}
		if(StringUtil.isNotBlank(entity.getAuditorName())){
			sql.append(" and  tt1.regional_director = ?  ");
			sqlPageQuery.addParam(entity.getAuditorName());
		}
		if(StringUtil.isNotBlank(entity.getOperatorName())){ 
			sql.append(" and tt1.user_name like ? ");
			sqlPageQuery.addLikeParam(entity.getOperatorName()); 
		}
		if(StringUtil.isNotBlank(entity.getIsDimission())){
			sql.append(" AND  tt1.join_status = ?  ");
			sqlPageQuery.addParam(Integer.valueOf(entity.getIsDimission()));
		}
		if(StringUtil.isNotBlank(entity.getJoinDateStart())){
			sql.append(" AND  tt1.join_date>=?");
			sqlPageQuery.addParam(entity.getJoinDateStart());
		}
		if(StringUtil.isNotBlank(entity.getJoinDateEnd())){
			sql.append(" AND  tt1.join_date<=?  ");
			sqlPageQuery.addParam(entity.getJoinDateEnd());
		}
		if(StringUtil.isNotBlank(entity.getPostName())){
			sql.append(" AND  tt1.post_name =?  ");
			sqlPageQuery.addParam(entity.getPostName());
		}
		sql.append(" order by monthSum desc ");
		sqlPageQuery.setQuerySql(sql.toString());
		sqlPageQuery.setSumColumnIndexs(new int[] {6,7,8,9,10,11,12,13,14,15});
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	@Override
	public PageResult<Object[]> getTeamTotalSectionByPageForExcle(ProvinceTeamTotalForm entity) {
		StringBuffer  sql=new StringBuffer();
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		sql.append(bidmDao.getSql("sql.ReportStatServiceImpl.getTeamTotalSectionByPageForExcle"));
		sqlPageQuery.addParams(new  Object[]{entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd()});
		// sql.append(" where 1=1 ");
		if(StringUtil.isNotBlank(entity.getProvince())){
			sql.append(" and  tt1.province_name = ? ");
			sqlPageQuery.addParam(entity.getProvince());
		}
		if(StringUtil.isNotBlank(entity.getAuditorNameNo())){ 
			sql.append(" and tt1.regional_director_no = ? ");
			sqlPageQuery.addParam(entity.getAuditorNameNo());
		}
	
		if(StringUtil.isNotBlank(entity.getIsDimission())){
			sql.append(" AND  tt1.regional_director_join_status = ?  ");
			sqlPageQuery.addParam(Integer.valueOf(entity.getIsDimission()));
		}
		
		if(StringUtil.isNotBlank(entity.getJoinDateStart())){
			sql.append(" AND  tt1.regional_director_join_date>=?");
			sqlPageQuery.addParam(entity.getJoinDateStart());
		}
		if(StringUtil.isNotBlank(entity.getJoinDateEnd())){
			sql.append(" AND  tt1.regional_director_join_date<=?  ");
			sqlPageQuery.addParam(entity.getJoinDateEnd());
		}
		if(StringUtil.isNotBlank(entity.getPostName())){
			sql.append(" AND  tt1.regional_director_post_name =?  ");
			sqlPageQuery.addParam(entity.getPostName());
		}
		sql.append(" GROUP BY tt1.province_name,tt1.regional_director ORDER BY SUM(tt1.sumLoan) DESC ");
		sqlPageQuery.setQuerySql(sql.toString());
		
		sqlPageQuery.setSumColumnIndexs(new int[] {5,6,7,8});
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	
	@Override
	public PageResult<Object[]> getProvinceTotalSectionByPage(ProvinceTeamTotalForm entity) {
		StringBuffer  sql=new StringBuffer();
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		sql.append(bidmDao.getSql("sql.ReportStatServiceImpl.getProvinceTotalSectionByPage"));
		sqlPageQuery.addParams(new  Object[]{entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd()});
		if(StringUtil.isNotBlank(entity.getProvince())){
			sql.append(" and  t.province_name = ? ");
			sqlPageQuery.addParam(entity.getProvince());
		}
		sql.append(" group by t.province_name order by sumLoan desc");
		sqlPageQuery.setQuerySql(sql.toString());
		sqlPageQuery.setSumColumnIndexs(new int[] { 1,2,3,4 });
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	
	@Override
	public PageResult<Object[]> getOperatorTotalSectionByPageForExcle(ProvinceTeamTotalForm entity) {
		StringBuffer  sql=new StringBuffer();
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		sql.append(bidmDao.getSql("sql.ReportStatServiceImpl.getOperatorTotalSectionByPageForExcle"));
		sqlPageQuery.addParams(new  Object[]{entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd(),entity.getLoanDateStart(),entity.getLoanDateEnd()});
		if(StringUtil.isNotBlank(entity.getProvince())){
			sql.append(" and  tt1.province_name = ? ");
			sqlPageQuery.addParam(entity.getProvince());
		}
		if(StringUtil.isNotBlank(entity.getAuditorNameNo())){
			sql.append(" and  tt1.regional_director_no = ?  ");
			sqlPageQuery.addParam(entity.getAuditorNameNo());
		}
		if(StringUtil.isNotBlank(entity.getAuditorName())){
			sql.append(" and  tt1.regional_director = ?  ");
			sqlPageQuery.addParam(entity.getAuditorName());
		}
		if(StringUtil.isNotBlank(entity.getOperatorName())){ 
			sql.append(" and tt1.user_name like ? ");
			sqlPageQuery.addLikeParam(entity.getOperatorName()); 
		}
		if(StringUtil.isNotBlank(entity.getIsDimission())){
			sql.append(" AND  tt1.join_status=?  ");
			sqlPageQuery.addParam(entity.getIsDimission());
		}
		if(StringUtil.isNotBlank(entity.getJoinDateStart())){
			sql.append(" AND  tt1.join_date>=?");
			sqlPageQuery.addParam(entity.getJoinDateStart());
		}
		if(StringUtil.isNotBlank(entity.getJoinDateEnd())){
			sql.append(" AND  tt1.join_date<=?  ");
			sqlPageQuery.addParam(entity.getJoinDateEnd());
		}
		if(StringUtil.isNotBlank(entity.getPostName())){
			sql.append(" AND  tt1.post_name =?  ");
			sqlPageQuery.addParam(entity.getPostName());
		}
		sql.append(" order by sumLoan desc ");
		sqlPageQuery.setQuerySql(sql.toString());
		sqlPageQuery.setSumColumnIndexs(new int[] { 6,7,8,9});
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	
	
	
	@Override
	public PageResult<Object[]> getBankLimitByPage(ProvinceTeamTotalForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(),entity.getO2oIndex());
		String sql = bidmDao.getSql("sql.ReportStatServiceImpl.getBankLimitByPage");
		sqlPageQuery.setQuerySql(sql);
		sqlPageQuery.setSumColumnIndexs(new int[] { 1,2,3,4,5,6,7,8,9,10,11 });
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	
	@Override
	public List<Object[]> getLikePostList(ProvinceTeamTotalForm entity) {
		String sql = "select t.code,t.name  from wf_dictionary_data t where t.type=16  and  t.name  LIKE ? LIMIT 10";
		return webAdminDao.findObjectsListForWorkflow(sql, "%" + entity.getPostName() + "%");
	}
	
	
	@Override
	public List<Object[]>  getLikeEmployeeList(String empName){
		String sql = "SELECT employee_no, employee_namecn, CONCAT(employee_code,employee_account) FROM o2o_employee WHERE (employee_namecn LIKE ? OR employee_code LIKE ?) LIMIT 10";
		return webAdminDao.findObjectsListForWorkflow(sql, "%" + empName + "%", "%" + empName + "%");
		
	}
	
	
	@Override
	public PageResult<Object[]> getLoanAmountByProvince(LoanForProvinceForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		ObjectSqlBuilder objectSqlBuilder = ObjectSqlBuilder.create(entity, ConfigContext.getStringValue("sql.ReportStatServiceImpl.loanAmountByProvince"));
		sqlPageQuery.setQuerySql(objectSqlBuilder.getSql()+" group by province_name order by loanamount DESC ");
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.setSumColumnIndexs(new int[] { 1});
		sqlPageQuery.addParams(objectSqlBuilder.getParams());
		return bidmDao.findDataWithPage(sqlPageQuery);
	}
	
	
	@Override
	public PageResult<Object[]> getBankreFundTotalSectionByPage(ProvinceTeamTotalForm entity){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		ObjectSqlBuilder objectSqlBuilder = ObjectSqlBuilder.create(entity, ConfigContext.getStringValue("sql.ReportStatServiceImpl.bankreFundTotalSection"));
		sqlPageQuery.setQuerySql(objectSqlBuilder.getSql()+" group by bank_name order by loan_amount DESC");
		sqlPageQuery.addParam(entity.getLoanDateStart());
		sqlPageQuery.addParam(entity.getLoanDateEnd());
		sqlPageQuery.setSumColumnIndexs(new int[] { 1,2,3,4});
		return bidmDao.findDataWithPage(sqlPageQuery);
		
	}
}
